import datetime
import json

import openpyxl
import requests
import pymysql
import time
import re
from lxml import etree
from selenium import webdriver
import sys

from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from quote_folder.Db_connect import DB_connect
from selenium.webdriver.common.by import By


class UpDateStatus:
    def __init__(self):
        self.url = "http://cpquery.cnipa.gov.cn/"
        self.bigdata_con = DB_connect().online_big_data()
        self.bigdata_cur = self.bigdata_con.cursor()
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.redis_con = DB_connect().patents_reids()

    def get_login(self):
        headers = {"Accept": "application/json"}
        url = 'https://api.xinhehonggu.com/api/auth/login'
        phone = '18866668888'
        password = 'xhhg2019'
        source = 'admin'
        data = {
            'method': '1',
            'phone': phone,
            'password': password,
            'source': source,
        }
        r = requests.post(url=url, data=data, headers=headers, verify=False)
        res_dict = r.json()
        token = res_dict['data']['token']
        return token

    def connenc_host(self, item, num):
        token = self.get_login()
        headers = {"Accept": "application/json"}
        url = "https://api.xinhehonggu.com/api/external_order/update"
        # url = "http://test.api.whxhhgip.com/api/external_order/update"  # 测试接口
        data = {
            'token': token,
            'secret': 'th45gdk1df5rdb51',
            "patentNo": item['sqh'],  # 申请号
            'lawStatus': item['state']  # 案件状态
        }

        res = requests.put(url=url, headers=headers, data=data)
        jsondata = json.loads(res.text)
        print(jsondata)
        num += 1
        if jsondata['status'] == 'error':
            try:
                if jsondata['message'] == '专利号不存在':
                    sqh = item['sqh']
                    url = "https://api.xinhehonggu.com/api/external_order"
                    # url = "http://test.api.whxhhgip.com/api/external_order"  # 测试接口
                    sql = f'SELECT inventor, digest FROM `patent_table` WHERE application_number = "{sqh}"'
                    try:
                        self.bigdata_cur.execute(sql)
                        returndata = self.bigdata_cur.fetchall()
                        inventor = returndata[0][0]
                        degist = returndata[0][1]
                    except:
                        inventor = '无'
                        degist = '无'
                        degist = ''
                    apply_time = item['apply_day']
                    try:
                        time = item['impower_day']
                        data_1 = {
                            'secret': 'th45gdk1df5rdb51',
                            "patentNo": item['sqh'],  # 申请号
                            'lawStatus': item['state'] if item['state'] else '',  # 案件状态
                            'applyData': apply_time if type(apply_time) is str else apply_time.strftime("%Y-%m-%d"),
                            'typeTitle': item['type'],
                            'title': item['name'],
                            'abstract': item['abstract'] if item['abstract'] else '无',
                            'patentee': item['patentee'],
                            'inventor': item['inventor'] if item['inventor'] else '无',
                            'grantDate': time if type(time) is str else time.strftime("%Y-%m-%d"),
                            'organization': item['agent'] if item['agent'] else ''
                        }
                    except:
                        data_1 = {
                            'secret': 'th45gdk1df5rdb51',
                            "patentNo": item['sqh'],  # 申请号
                            'lawStatus': item['state'] if item['state'] else '无状态',  # 案件状态
                            'applyData': apply_time if type(apply_time) is str else apply_time.strftime("%Y-%m-%d"),
                            'typeTitle': item['type'],
                            'title': item['name'],
                            'abstract': item['abstract'] if item['abstract'] else '无',
                            'patentee': item['patentee'],
                            'inventor': item['inventor'] if item['inventor'] else '无',
                            # 'grantDate': time if type(time) is str else time.strftime("%Y-%m-%d"),
                            'organization': item['agent'] if item['agent'] else ''
                        }
                    print("data_1: ", data_1)
                    res1 = requests.post(url=url, headers=headers, data=data_1)
                    print("~~", json.loads(res1.text))
            except:
                print("``````````````", jsondata)

        print("-" * 100)
        print("\n")

    def get_sqh_excel(self, path):
        # 读取excel信息并返回列表
        inwb = openpyxl.load_workbook(path)
        ws = inwb[inwb.sheetnames[0]]  # 获取第一个sheet内容
        # 获取sheet的最大行数和列数
        rows = ws.max_row
        cols = ws.max_column
        all_data_list = []
        location_dict = {}
        for r in range(1, rows + 1):
            row_list = []
            for c in range(1, cols + 1):
                cell_value = ws.cell(r, c).value
                if cell_value == '申请号':
                    location_dict['sqh'] = c - 1
                if cell_value == '专利名称':
                    location_dict['name'] = c - 1
                if cell_value == '专利权人':
                    location_dict['patentee'] = c - 1
                if cell_value == '申请日':
                    location_dict['apply_day'] = c - 1
                if cell_value == '授权日':
                    location_dict['impower_day'] = c - 1
                if cell_value == '专利类型':
                    location_dict['type'] = c - 1
                if cell_value == '专利状态':
                    location_dict['state'] = c - 1
                if cell_value == '代理机构':
                    location_dict['agent'] = c - 1
                if cell_value == '发明人':
                    location_dict['inventor'] = c - 1
                if cell_value == '摘要':
                    location_dict['abstract'] = c - 1
                row_list.append(cell_value)
            all_data_list.append(row_list)
        return all_data_list

    def run(self):
        """"""
        path = r'工程学院.xlsx'
        returndata = self.get_sqh_excel(path)  # 获取excel表中的数据
        num = 0
        for i in range(1, len(returndata)):
            num += 1
            if returndata[i][4]:
                data = {
                    'sqh': 'CN' + str(returndata[i][0])[0:12] + "." + str(returndata[i][0])[-1],
                    'name': returndata[i][1],
                    'patentee': returndata[i][2],
                    'apply_day': returndata[i][3],
                    'impower_day': returndata[i][4],
                    'type': returndata[i][5],
                    'state': returndata[i][6],
                    'agent': returndata[i][7],
                    'inventor': returndata[i][8],
                    'abstract': returndata[i][9],
                }
            else:
                data = {
                    'sqh': 'CN' + str(returndata[i][0])[0:12] + "." + str(returndata[i][0])[-1],
                    'name': returndata[i][1],
                    'patentee': returndata[i][2],
                    'apply_day': returndata[i][3],
                    'type': returndata[i][5],
                    'state': returndata[i][6],
                    'agent': returndata[i][7],
                    'inventor': returndata[i][8],
                    'abstract': returndata[i][9],
                }
            self.connenc_host(data, num)
        self.bigdata_con.commit()


if __name__ == '__main__':
    uds = UpDateStatus
    uds().run()
#     2020207090033
